package com.nkzy.dao.impl;

import com.nkzy.dao.UserDao;
import com.nkzy.domain.User;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Create by 王建烨 on 2022/4/15 11:10
 **/

@Repository   // 持久层注解
public class UserDaoImpl implements UserDao {

    @Resource // 依赖注入
    private JdbcTemplate jdbcTemplate;

    /**
     * 添加用户
     *
     * @param user 用户实例
     */
    @Override
    public void insertUser(User user) {
        String sql = "insert into user (username, usersex) values (?, ?)";
        jdbcTemplate.update(sql, user.getUsername(), user.getUsersex());
    }

    /**
     * 查询所有
     *
     * @return 用户列表
     */
    @Override
    public List<User> queryAllUser() {
        String sql = "select * from user";
        return getUserList(sql);
    }

    /**
     * 查询单个用户
     * @param id 用户id
     * @return 用户实例
     */
    @Override
    public User query(Integer id) {
        String sql = "select * from user where userid=?";
        User user = jdbcTemplate.queryForObject(sql, (rs, rowNum) -> {
            User user1 = new User();
            user1.setId(rs.getInt("userid"));
            user1.setUsername(rs.getString("username"));
            user1.setUsersex(rs.getString("usersex"));
            return user1;
        }, id);
        return user;
    }

    @Override
    public void delete(Integer id) {
        String sql = "delete from user where userid=?";
        jdbcTemplate.update(sql, id);
    }

    /**
     * 更新用户
     * @param user 用户实例
     */
    @Override
    public void update(User user) {
        String sql = "update user set username=?,usersex=? where userid=?";
        jdbcTemplate.update(sql, user.getUsername(), user.getUsersex(), user.getId());
    }

    /**
     * 分页
     *
     * @param pageSize 每页数量
     * @param page     第几页
     * @return 用户列表
     */
    @Override
    public List<User> queryUserByPagination(Integer pageSize, Integer page) {
        int limit = (page - 1) * pageSize;
        String sql = String.format("select * from user limit %d,%d", limit, pageSize);
        return getUserList(sql);
    }

    /**
     * 查询总数
     * @param pageSize 每页数量
     * @return 总数
     */
    @Override
    public int queryTotalPagination(Integer pageSize) {
        // 查询user表总页数
        String sql = "select ceil(count(*) / ?) from user";
        Integer totalPage = jdbcTemplate.queryForObject(sql, Integer.class, pageSize);
        return totalPage;
    }

    /**
     * 获取用户列表
     *
     * @param sql 查询语句
     * @return 用户列表
     */
    private List<User> getUserList(String sql) {
        // 获取用户列表
        List<User> userList = jdbcTemplate.query(sql, (rs, rowNum) -> {
            User user = new User();
            user.setId(rs.getInt("userid"));
            user.setUsername(rs.getString("username"));
            user.setUsersex(rs.getString("usersex"));
            return user;
        });
        return userList;
    }
}
